﻿CREATE PROCEDURE [dbo].[spMomPortRma]( @OwnerID int )
with recompile
as
begin
	set nocount on
	declare @message nvarchar(1000)
	set @message = 'MOMPortRma' + '  ' + convert( varchar, getdate(), 9 )
	RAISERROR (@message, 0, 1) WITH NOWAIT

	declare @orderCode char(1)
	set @orderCode = 'E'

	insert into codeReturnType ( OwnerID, Description, ReturnToInventory, Active )
	select @OwnerID, [desc], backtoinv, 1
	from openquery( MOMPort, 'select * from retcodes' ) as T1
	where not exists ( select 1 from codeReturnType where [Description] = T1.[desc]
		and coalesce( OwnerID, @OwnerID ) = @OwnerID )

	insert into tblRmaReturn ( OrderID, IssueDate, ReceiptDate, ReturnTypeID, SourceNumber )
	select T2.OrderID, dbo.SmallDTCast(T1.[entered]), dbo.SmallDTCast(T1.[procdate]), min( T3.ReturnTypeID ), T1.[rmanumber]
	from openquery( MOMPort, 'select T1.rmanumber, T1.order, T1.entered, T1.procdate, T3.desc from rma T1
		join rmaitems T2 on T1.rmanumber = T2.rmanumber
		join retcodes T3 on T2.ret_code = T3.ret_code' ) as T1
		join tblOrderSourceID T2 on cast(T1.[order] as varchar(20)) = T2.IDValue
			and T2.IDSourceCode = @orderCode
			and T2.OwnerID = @OwnerID
		join codeReturnType T3 on T1.[desc] = T3.[Description]
			and coalesce( T3.OwnerID, @OwnerID ) = @OwnerID
	group by T2.OrderID, T1.[entered], T1.[procdate], T1.[rmanumber]
	create index idx_tblRmaTmp on tblRmaReturn ( SourceNumber )

	insert into tblReturnDetail( RmaReturnID, Qty, OrderDetailID )
	select T2.RmaReturnID, T1.quantity, T4.OrderDetailID
	from openquery( MOMPort, 'select T1.quantity, T1.rmanumber, T2.item from rmaitems T1
		join items T2 on T1.item_id = T2.item_id' ) T1
		join tblRmaReturn T2 on T1.rmanumber = T2.SourceNumber
		join tblProduct T3 on T1.item = T3.ItemCode and T3.OwnerID = @OwnerID
		join tblOrderDetail T4 on T2.OrderID = T4.OrderID
			and T4.ProductID = T3.ProductID

end
